#!/usr/bin/env python3
#-*- coding:utf-8 -*-
import pymysql
import taos
from taosrest import connect, TaosRestConnection, TaosRestCursor
import time

con_mysql = pymysql.connect(host='127.0.0.1',
                        database='location', 
                        user='root', 
                        password='password',
                        charset='utf8')

con_taosrest: TaosRestConnection = connect(url="http://127.0.0.1:6041",
                        user="root",
                        password="password",
                        timeout=30)

"""
DROP TABLE IF EXISTS location.location;
CREATE STABLE location.location ( \
    locationtime TIMESTAMP, \
    lat DOUBLE, \
    lng DOUBLE, \
    speed DOUBLE) \
    TAGS (loginname NCHAR(50), \
      username NCHAR(50), \
      deviceimei NCHAR(50), \
      devicemodel NCHAR(100), \
      devicetype BOOL);
"""

start_time = time.time()
cursor_mysql = con_mysql.cursor()
cursor_mysql.execute('SELECT CONCAT(\'t\',LoginName,\'_\',DeviceIMEI) AS tbname , \
    LoginName, \
    MAX(UserName) AS UserName, \
    DeviceIMEI, \
    IFNULL(MAX(DeviceModel),\'\') AS DeviceModel, \
    IFNULL(MAX(DeviceType),\'\') AS DeviceType\
    FROM location.location \
    GROUP BY LoginName,DeviceIMEI;')
tbnamecur = cursor_mysql.fetchall()
cursor_mysql.close()

for tbname,loginname,username,deviceimei,devicemodel,devicetype in tbnamecur:
    table_start_time = time.time()
    cursor_mysql = con_mysql.cursor(cursor=pymysql.cursors.DictCursor)
    cursor_mysql.execute('SELECT Id FROM location.location WHERE LoginName = \"%s\" ORDER BY Id DESC;' % loginname) 
    countlines = cursor_mysql.fetchall()
    cursor_mysql.close()
    count = countlines[0]['Id']
 
    i = 0
    j = 10
    taoscnt = 0
    for i in range(0, count, 100):
        cursor_mysql = con_mysql.cursor(cursor=pymysql.cursors.DictCursor)
        cursor_mysql.execute(f'SELECT CONCAT(\'t\',LoginName,\'_\',DeviceIMEI) AS tbname, \
          LocationTime, \
          Lat, \
          Lng, \
          Speed \
        FROM location.location \
        WHERE LoginName = \"%s\" \
          AND Id > %d \
          AND Id <= %d;' % (loginname,i,j))
        submission = cursor_mysql.fetchall()
        cursor_mysql.close()
        sql="INSERT INTO location.`%s` USING location.location TAGS (\'%s\',\'%s\',\'%s\',\'%s\',%s) VALUES " % (tbname,loginname,username,deviceimei,devicemodel,devicetype)
        if submission:
            for row in submission:
                sql += '(\'{}\',{},{},{}) '.format(row["LocationTime"],row["Lat"],row["Lng"],row["Speed"])
            sql += ";"
            #print(sql)
            cursor_taosrest: TaosRestCursor = con_taosrest.cursor()
            cursor_taosrest.execute(sql)
            taoscnt += cursor_taosrest.rowcount
            cursor_taosrest.close()
        else:
            i += 10
            j += 10
            continue
        i += 10
        j += 10
        print("子表'%s'已迁移%i行!" % (tbname,taoscnt))
    table_end_time = time.time()
    deltatime = table_end_time - table_start_time
    totalhour = int(deltatime / 3600)
    totalminute = int((deltatime - totalhour * 3600) / 60)
    totalsecond = int(deltatime - totalhour * 3600 - totalminute * 60)
    print("子表'%s'数据迁移完毕,耗时:%d小时%d分%d秒!" %(tbname,totalhour, totalminute, totalsecond))
end_time = time.time()
deltatime = end_time - start_time
totalhour = int(deltatime / 3600)
totalminute = int((deltatime - totalhour * 3600) / 60)
totalsecond = int(deltatime - totalhour * 3600 - totalminute * 60)
print("数据全部迁移完毕,总计耗时:%d小时%d分%d秒!" %(totalhour, totalminute, totalsecond))

con_mysql.close()
con_taosrest.close()